﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using DAO_DTO_WS.DTO;
using System.Data.SqlClient;
namespace DAO_DTO_WS.DAO
{
    public class ThanhVienDAO : Abstract
    {
        public static bool ThemMoi(ThanhVienDTO thanhVien)
        {
            SqlConnection connection = KetNoi();
            bool KetQua = false;
            String SqlString = "INSERT INTO THANHVIEN(Email,TenThanhVien,MatKhau,HoTen,GioiTinh,NgaySinh,DienThoai,NoiO,NguyenQuan,TaiKhoan,Avatar,TinhTrang) VALUES " +
                 "(@Email,@TenThanhVien,@MatKhau,@HoTen,@GioiTinh,@NgaySinh,@DienThoai,@NoiO,@NguyenQuan,@TaiKhoan,@Avatar,@TinhTrang)";
            try
            {
                SqlCommand command = new SqlCommand(SqlString, connection);
                command.Parameters.AddWithValue("@Email", thanhVien.Email);
                command.Parameters.AddWithValue("@TenThanhVien", thanhVien.TenThanhVien);
                command.Parameters.AddWithValue("@MatKhau", thanhVien.MatKhau);
                command.Parameters.AddWithValue("@HoTen", thanhVien.HoTen);
                command.Parameters.AddWithValue("@GioiTinh", thanhVien.GioiTinh);
                command.Parameters.AddWithValue("@NgaySinh", thanhVien.NgaySinh);
                command.Parameters.AddWithValue("@DienThoai", thanhVien.DienThoai);
                command.Parameters.AddWithValue("@NoiO", thanhVien.NoiO);
                command.Parameters.AddWithValue("@NguyenQuan", thanhVien.NguyenQuan);
                command.Parameters.AddWithValue("@TaiKhoan", thanhVien.TaiKhoan);
                command.Parameters.AddWithValue("@Avatar", thanhVien.Avatar);
                command.Parameters.AddWithValue("@TinhTrang", thanhVien.TinhTrang);
                List<ThanhVienDTO> DanhSach = new List<ThanhVienDTO>();
                DanhSach = ThanhVienDAO.LayDSThanhVien();
                int duocThem = 1;
                int rows = 0;
                for (int i = 0; i < DanhSach.Count; i++)
                {
                    if (thanhVien.TenThanhVien == DanhSach[i].TenThanhVien)
                    {
                        KetQua = false;
                        duocThem = 0;
                    }
                }
                if (duocThem == 1)
                {
                    rows = command.ExecuteNonQuery();
                    if (rows == 1)
                        KetQua = true;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return KetQua;
        }
        public static List<ThanhVienDTO> LayDSThanhVienTheoLoai(int loai)
        {
            List<ThanhVienDTO> dstv = new List<ThanhVienDTO>();
            dstv = LayDSThanhVien();
            List<ThanhVienDTO> kq = new List<ThanhVienDTO>();
            for (int i = 0; i < dstv.Count; ++i)
                if (loai == 0)
                {
                    if (dstv[i].TinhTrang < 2)
                        kq.Add(dstv[i]);
                }
                else if (dstv[i].TinhTrang == loai - 1)
                    kq.Add(dstv[i]);
            return kq;
        }
        public static List<ThanhVienDTO> LayDSThanhVien()
        {
            SqlConnection connection = KetNoi();
            List<ThanhVienDTO> dstv = new List<ThanhVienDTO>();
            String SqlString = "select * from THANHVIEN";
            try
            {
                SqlCommand lenh = new SqlCommand(SqlString, connection);
                SqlDataReader Doc = lenh.ExecuteReader();
                while (Doc.Read())
                {
                    ThanhVienDTO thanhvien = new ThanhVienDTO();
                    DocDuLieu(Doc,thanhvien);
                    
                    dstv.Add(thanhvien);
                }
            }
            catch (Exception ex)
            {
                throw ex;
                dstv = new List<ThanhVienDTO>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dstv;
        }
        private static void DocDuLieu(SqlDataReader Doc,ThanhVienDTO thanhvien)
        {
            thanhvien.MaThanhVien = Doc.GetInt32(0);
            thanhvien.Email = Doc.GetString(1);
            thanhvien.TenThanhVien = Doc.GetString(2);
            thanhvien.MatKhau = Doc.GetString(3);
            thanhvien.HoTen = Doc.GetString(4);
            thanhvien.GioiTinh = Doc.GetInt32(5);
            thanhvien.NgaySinh = Doc.GetDateTime(6);
            thanhvien.DienThoai = Doc.GetString(7);
            thanhvien.NoiO = Doc.GetString(8);
            thanhvien.NguyenQuan = Doc[9].ToString();
            thanhvien.TaiKhoan = Convert.ToInt64(Doc[10]);
            thanhvien.Avatar = Doc[11].ToString();
            thanhvien.TinhTrang = Convert.ToInt32(Doc[12]);
        }
        public static ThanhVienDTO LayThanhVien(string tendangnhap)
        {
            SqlConnection connection = KetNoi();
            ThanhVienDTO tv = new ThanhVienDTO();
            String SqlString = "select * from THANHVIEN where TenThanhVien=@tendangnhap";
            try
            {
                SqlCommand lenh = new SqlCommand(SqlString, connection);
                lenh.Parameters.AddWithValue("@tendangnhap", tendangnhap);
                SqlDataReader Doc = lenh.ExecuteReader();
                bool kt = Doc.Read();
                if (kt)
                    DocDuLieu(Doc, tv);
                else
                {
                    tv = new ThanhVienDTO();
                    tv.TinhTrang = -1;
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return tv;
        }
        public static ThanhVienDTO LayThanhVienTheoMa(int maThanhVien)
        {
            SqlConnection connection = KetNoi();
            ThanhVienDTO tv = new ThanhVienDTO();
            String SqlString = "select * from THANHVIEN where MaThanhVien=@MaThanhVien";
            try
            {
                SqlCommand lenh = new SqlCommand(SqlString, connection);
                lenh.Parameters.AddWithValue("@MaThanhVien", maThanhVien);
                SqlDataReader Doc = lenh.ExecuteReader();
                bool kt = Doc.Read();
                if (kt)
                    DocDuLieu(Doc, tv);
                else
                {
                    tv = new ThanhVienDTO();
                    tv.TinhTrang = -1;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return tv;
        }
        public static bool SuaTV(ThanhVienDTO thanhVien)
        {
            SqlConnection connection = KetNoi();
            bool KetQua = false;
            String SqlString = "UPDATE THANHVIEN " +
                "SET Email=@Email,TenThanhVien=@TenThanhVien,MatKhau=@MatKhau,HoTen=@Hoten,GioiTinh=@GioiTinh,NgaySinh=@NgaySinh,DienThoai=@DienThoai,NoiO=@NoiO,NguyenQuan=@NguyenQuan,TaiKhoan=@TaiKhoan,Avatar=@Avatar,TinhTrang=@TinhTrang " +
                "WHERE MaThanhVien=@MaThanhVien";

            try
            {
                SqlCommand command = new SqlCommand(SqlString, connection);
                command.Parameters.AddWithValue("@Email", thanhVien.Email);
                command.Parameters.AddWithValue("@TenThanhVien", thanhVien.TenThanhVien);
                command.Parameters.AddWithValue("@MatKhau", thanhVien.MatKhau);
                command.Parameters.AddWithValue("@HoTen", thanhVien.HoTen);
                command.Parameters.AddWithValue("@GioiTinh", thanhVien.GioiTinh);
                command.Parameters.AddWithValue("@NgaySinh", thanhVien.NgaySinh);
                command.Parameters.AddWithValue("@DienThoai", thanhVien.DienThoai);
                command.Parameters.AddWithValue("@NoiO", thanhVien.NoiO);
                command.Parameters.AddWithValue("@NguyenQuan", thanhVien.NguyenQuan);
                command.Parameters.AddWithValue("@TaiKhoan", thanhVien.TaiKhoan);
                command.Parameters.AddWithValue("@Avatar", thanhVien.Avatar);
                command.Parameters.AddWithValue("@TinhTrang", thanhVien.TinhTrang);
                command.Parameters.AddWithValue("@MaThanhVien", thanhVien.MaThanhVien);
                
                int rows = 0;
                rows = command.ExecuteNonQuery();
                if (rows == 1)
                    KetQua = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return KetQua;
        }
        public static bool XoaTV(int maThanhVien)
        {
            SqlConnection connection = KetNoi();
            bool KetQua = false;
            String SqlString = "DELETE FROM THANHVIEN WHERE MaThanhVien=@MaThanhVien";
            try
            {
                SqlCommand command = new SqlCommand(SqlString, connection);
                command.Parameters.AddWithValue("@MaThanhVien", maThanhVien);
                int rows = 0;
                rows = command.ExecuteNonQuery();
                if (rows == 1)
                    KetQua = true;
            }
            catch (Exception ex)
            {
                throw ex;
                KetQua = false;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return KetQua;
        }
        public static bool KiemTra(string tendangnhap)
        {
            SqlConnection connection = KetNoi();
            ThanhVienDTO tv = new ThanhVienDTO();
            bool kt = false;
            String SqlString = "select * from THANHVIEN where TenThanhVien=@tendangnhap";
            try
            {
                SqlCommand lenh = new SqlCommand(SqlString, connection);
                lenh.Parameters.AddWithValue("@tendangnhap", tendangnhap);
                SqlDataReader Doc = lenh.ExecuteReader();
                kt = Doc.Read();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return kt;
        }
    }
}
